﻿using NORM.Common;
using NORM.Entity;
using NORM.SQLObject; 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;
using System.Data.SqlClient;
using NORM.Configure;

namespace NORM.DataBase
{
    internal class SqlDataBase : DataBase, IDataBase
    {
        protected bool TransactionBegin = false;
        protected SqlHelper sqlserver = new SqlHelper();

        private SqlTransaction tran;
        private SqlConnection conn = new SqlConnection();

        internal SqlDataBase()
        {
            this._sql = string.Empty;
            try
            {
                this._connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
                //解析连接字符串，解密
                if (string.IsNullOrEmpty(this._connectionString))
                    throw new Exception("连接字符串不能为空");
                SectionString[] SectionsString = Parse(this._connectionString);
                this._connectionString = Join(SectionsString);
                this._dataBaseType = DataBaseTypes.SqlDataBase;
                //初始化 conn
                this.conn = new SqlConnection(_connectionString);
            }
            catch (Exception ex)
            {
                throw new NORMException(ExceptionType.ConfigException, ex.Message);
            }
        }

        internal SqlDataBase(string connectionString)
        {
            this._sql = string.Empty;
            this._connectionString = connectionString; //System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
            //解析连接字符串，解密
            SectionString[] SectionsString = Parse(this._connectionString);
            this._connectionString = Join(SectionsString);
            this._dataBaseType = DataBaseTypes.SqlDataBase;
            //初始化 conn
            this.conn = new SqlConnection(_connectionString);
           
        }

        #region 私有方法

        int execute(OQL oql)
        {
            int val = -1;

            SetCommand(oql);

            if (TransactionBegin)
                val = sqlserver.Execute(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                val = sqlserver.Execute(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            return val;
        }

        bool insert<T>(T model) where T : EntityBase
        {
            bool rvl = false;
            if (model != null)
            {
                OQL oql = new OQL(model).Insert().End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }

            }
            return rvl;
        }

        bool update<T>(T model) where T : EntityBase
        {
            bool rvl = false;

            if (model != null)
            {
                OQL oql = new OQL(model).Update().End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }
            }            

            return rvl;
        }

        bool delete<T>(T model) where T : EntityBase
        {
            bool rvl = false;

            if (model != null)
            {
                List<Condition> conditions = new List<Condition>();

                Type _type = model.GetType();
                PropertyInfo[] properties = CacheProperties.cacheInstance.GetProperties(_type);  //_type.GetProperties();
                foreach (PropertyInfo pi in properties)
                {
                    if (pi.Name != "TableName" && pi.Name != "Properties")
                    {
                        object valud_d = pi.GetValue(model, null);
                        if (valud_d != null && DBNull.Value != valud_d)
                        {
                            Condition c = new Condition();
                            c.Field = pi.Name;
                            c.Value = valud_d;
                            conditions.Add(c);
                        }
                    }
                }

                OQL oql = new OQL(model).Delete().Where(conditions.ToArray()).End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }

            }

            return rvl;
        }

        #endregion

        #region 设置Command命令

        private string sqlcommandtextParse(string sql)
        {
            string result = string.Empty;
            //处理Limit

            string pattern = "Limit [0-9]+";
            List<string> limitArray = new List<string>();

            System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(pattern,
                System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled);

            System.Text.RegularExpressions.Match math = reg.Match(sql);
            while (math.Success)
            {
                string limitStr = math.Groups[0] + "";
                limitArray.Add(limitStr);
                math = math.NextMatch();               
            }

            if(limitArray.Count >1)
                throw new NORMException(ExceptionType.DataBaseExceptoin, sql + " Limit 附近有语法错误");

            foreach (string limit in limitArray)
            {
                string _limit = limit.Replace("Limit ", "");
                sql = sql.Replace(limit, "").Replace("SELECT", "SELECT TOP " + _limit + "");
            }

            result = sql;

            return result;
        }

        public void SetCommand(OQL oql)
        {
            Type _type = oql.Modelobject.GetType();
            #region Model 获取对象类型 Table View Function
            string classTypeName = "";
            object[] attrs = _type.GetCustomAttributes(false);// typeof(T)
            if (attrs != null && attrs.Length > 0)
            {
                foreach (object attr in attrs)
                {
                    if (attr.GetType().Equals(typeof(AtttribDescrib)))
                    {
                        classTypeName = attr.ToString();
                        break;
                    }
                }
            }
            #endregion  

            _sql = oql.ToString();
            if (_sql.Contains("Limit"))
            {
                if (oql.ChildOql != null && oql.ChildOql.sql.Contains("Limit"))
                    throw new NORMException(ExceptionType.DataBaseExceptoin, oql.sql + "Limit 附近有语法错误");   
                _sql = sqlcommandtextParse(_sql);                               
            }
            
            Command =new OsqlCommand();
            if(classTypeName=="View")
                Command.CommandText = _sql;
            else
                Command.CommandText = _sql;

            List<SqlParameter> _params = null;   

            if (oql.Parameters != null)
            {
                OsqlParameter[] osqlparams = oql.Parameters;
                switch (DataBaseType)
                {
                    case DataBaseTypes.SqlDataBase:
                        _params = new List<SqlParameter>();
                        foreach (OsqlParameter p in osqlparams)
                        {
                            SqlParameter p1 = new SqlParameter("@" + p.Name, p.Value);
                            _params.Add(p1);
                        }
                        Command.Parameters = _params.ToArray();
                        break;
                }
            }          

            base.validateOqlend(oql);
            //oql.Dispose();

        }

        #endregion

        #region 数据库对象

        public override DataTable GetDataBaseObject(string name, DataBaseSchema schema)
        {         
            DataTable dt = new DataTable();
            switch (schema)
            {
                case DataBaseSchema.Table:
                    dt = sqlserver.GetTableColumns(this._connectionString, name);
                    break;
                case DataBaseSchema.View:
                    dt = sqlserver.GetTableColumns(this._connectionString, name);
                    break;
            }
            return dt;
        }

        #endregion

        #region 事务操作

        public override void BeginTransaction()
        {
            conn = new SqlConnection(this._connectionString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            tran = conn.BeginTransaction();
            sqlserver.tran = tran;
            TransactionBegin = true;
        }

        public override int Commit()
        {
            try
            {
                tran.Commit();
                sqlserver.tran = null;
            }
            catch (Exception ex)
            {

            }
            TransactionBegin = false;
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return 1;
        }

        public override void RollBack()
        {
            try
            {
                tran.Rollback();
                sqlserver.tran = null;
            }
            catch
            {

            }
            TransactionBegin = false;
            if (conn.State != ConnectionState.Closed)
                conn.Close();
        }

        #endregion

        #region  执行SQL脚本

        public override int Execute(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return sqlserver.Execute(_connectionString, cmdType, cmdText, cmdParms);
        }

        public override DataSet GetDataSet(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return sqlserver.GetDataSet(_connectionString, cmdType, cmdText, cmdParms);
        }

        #endregion

        #region 执行OQL

        public override int Execute(OQL oql)
        {            
            return this.execute(oql);
        }

        public override bool Insert<T>(T model)
        {
            return this.insert<T>(model);
        }

        public override bool Update<T>(T model)
        {
            return this.update<T>(model);
        }

        public override bool Delete<T>(T model)
        {
            return this.delete<T>(model);
        }

        public override bool Delete<T>(object PrimaryKeyValue)
        {
            T model = (T)Activator.CreateInstance(typeof(T));
            System.Reflection.PropertyInfo pi = typeof(T).GetProperty(model.PrimaryKey[0]);
            if (pi == null) throw new Exception("没有设置主键");            
          
            OQL oql = new OQL(model).Delete().Where(new Condition[] { new Condition() { Field = pi.Name, Value = PrimaryKeyValue } }).End;

            return this.execute(oql) > 0 ? true : false;
        }

        public override List<T> QueryList<T>(OQL oql, PageLimit page)
        {
            SetCommand(oql);
            var list = new List<T>();
            try
            {

                string _TEMPSQL = Command.CommandText.TrimEnd(';');
                if (_TEMPSQL.Contains("ORDER BY"))
                {
                    _TEMPSQL = _TEMPSQL.Substring(0, _TEMPSQL.LastIndexOf("ORDER BY"));
                }

                string _total_sql = "SELECT COUNT(1) FROM (" +
                    _TEMPSQL + " ) tn ";

                var count = sqlserver.GetScalar(conn, CommandType.Text, _total_sql, Command.Parameters);

                int recordCount = 0;
                if (!Int32.TryParse(count + "", out recordCount))
                {
                    throw new NORMException(ExceptionType.OQLExceptin,"获取不到总记录数");
                }

                page.RecordCount = recordCount;
                page.PageCount = (int)(decimal.Ceiling((decimal)(page.RecordCount * 1.0 / page.PageSize)));
                
                if (page.PageCount <= 0) page.PageCount = 1;
                if (page.PageIndex <= 1) page.PageIndex = 1;
                if (page.PageIndex >= page.PageCount) page.PageIndex = page.PageCount;

                int startIndex = (page.PageIndex - 1) * page.PageSize;
                int endIndex = startIndex + page.PageSize;

                if (Command.CommandText.Contains("Limit") && oql.ChildOql == null)
                    throw new NORMException(ExceptionType.DataBaseExceptoin, "分页查询中不能再含有Limit");


                string SQL = "SELECT * FROM ( ";
                if (oql.Orderbies != null)
                {
                    SQL += "SELECT row_number() OVER ( ORDER BY ";
                    foreach (object o in oql.Orderbies)
                    {
                        string s = o.ToString();
                        //string[] array = o.ToString().Split(' ');
                        //string field = array[0] + "";
                        //string orderby = array[1] + "";

                        string f = string.Empty;
                        string b = string.Empty;
                        if (s.EndsWith("DESC"))
                        {
                            b = "DESC";
                            f = "[" + s.Replace("DESC", "").Trim() + "]";
                        }
                        else if (s.EndsWith("ASC"))
                        {
                            b = "ASC";
                            f = "[" + s.Replace("ASC", "").Trim() + "]";
                        }
                        else if (s.EndsWith("Asc"))
                        {
                            b = "Asc";
                            f = "[" + s.Replace("Asc", "").Trim() + "]";
                        }
                        else if (s.EndsWith("Desc"))
                        {
                            b = "Desc";
                            f = "[" + s.Replace("Desc", "").Trim() + "]";
                        }

                        SQL += f + " " + b + ",";

                    }
                    SQL = SQL.TrimEnd(',') + " ) AS rowId";
                }
                else
                {
                    SQL += "SELECT row_number() OVER ( ORDER BY ";
                    foreach (string s in oql.Modelobject.PrimaryKey)
                    {
                        string f = string.Empty;
                        string b = string.Empty;

                        b = "ASC";
                        f = "[" + s.ToString() + "]";
                        SQL += f + " " + b + ",";

                    }
                    SQL = SQL.TrimEnd(',') + " ) AS rowId";
                }

                SQL += "," + _TEMPSQL.Replace("SELECT", " ");
                SQL += " ) _LY WHERE rowId>=" + startIndex + " AND rowId<=" + endIndex + " ;";

                Command.CommandText = SQL;

                DataTable dt = new DataTable();
                if (TransactionBegin)
                    dt = sqlserver.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
                else
                    dt = sqlserver.GetDataTable(_connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

                list = ConvertTo.ConvertToList<T>(dt);

                //DbDataReader reader;
                //if (TransactionBegin)
                //    reader = (DbDataReader)sqlserver.GetReader(conn, CommandType.Text, Command.CommandText, Command.Parameters);
                //else
                //    reader = (DbDataReader)sqlserver.GetReader(conn, CommandType.Text, Command.CommandText, Command.Parameters);

                //var list = ConvertTo.ConvertToList<T>(reader);               
                //reader.Close();
                //reader.Dispose(); 

                page.DataSourceView = dt.DefaultView;
                oql.RecordCount = page.RecordCount;

            }
            catch (Exception ex)
            {
                throw new NORMException(ExceptionType.DataBaseExceptoin, ex.Message + "\r\n sql:" + Command.CommandText);
            }        

            return list;
        }

        public override List<T> QueryList<T>(OQL oql)
        {
            SetCommand(oql);

            //2016-01-29 之前
            //DataTable dt = new DataTable();
            //if (TransactionBegin)
            //    dt = sqlserver.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            //else
            //    dt = sqlserver.GetDataTable(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            //var list = ConvertTo.ConvertToList<T>(dt);
            //oql.RecordCount = list.Count;


            DbDataReader reader;
            if (TransactionBegin)
                reader = (DbDataReader)sqlserver.GetReader(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                reader = (DbDataReader)sqlserver.GetReader(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            
            var list = ConvertTo.ConvertToList<T>(reader);
            oql.RecordCount = list.Count;
            reader.Close();
            reader.Dispose();            
            
            return list;
        }

        public override T QueryObject<T>(OQL oql)
        {
            SetCommand(oql);

            T t = null; //(T)Activator.CreateInstance(typeof(T));// new T();
            if (!Command.CommandText.Contains("SELECT TOP"))
                Command.CommandText = Command.CommandText.Replace("SELECT", "SELECT TOP 1 ").Replace("Limit 1", "");
            else
                Command.CommandText = Command.CommandText.Replace("SELECT TOP " + oql._limit + " ", "SELECT TOP 1 ").Replace("Limit 1", "");
            
            DataTable dt = new DataTable();
            if (TransactionBegin)
                dt = sqlserver.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                dt = sqlserver.GetDataTable(_connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            List<T> list = ConvertTo.ConvertToList<T>(dt);
            foreach (T _t in list)
                t = _t;

            return t;           
        }

        public override T QueryModel<T>(object PrimaryKeyValue)
        {
            T t = (T)Activator.CreateInstance(typeof(T));// new T();

            if (t.PrimaryKey.Count <= 0)
                throw new Exception(t.TableName + "表还没有设置主键");

            OQL oql = new OQL(t).Select().Where(new Condition[] { 
                new Condition(){Field = t.PrimaryKey[0].ToString(), Comparison = "=", Value =PrimaryKeyValue } })
                .OrderBy(new string[] { t.PrimaryKey[0].ToString() }).End;

            SetCommand(oql); 
 
            t = QueryObject<T>(oql);

            return t;
        }

        public override DataTable QueryTable(OQL oql, PageLimit page = null)
        {
            DataTable dt = null;
            try
            {
                SetCommand(oql);

                if (page != null)
                {
                    string _total_sql = "SELECT COUNT(1) FROM (" +
                   Command.CommandText.TrimEnd(';') + " ) tn ";

                    var count = sqlserver.GetScalar(conn, CommandType.Text, _total_sql, Command.Parameters);

                    int recordCount = 0;
                    if (!Int32.TryParse(count + "", out recordCount))
                    {
                        return new DataTable();
                    }

                    page.RecordCount = recordCount;
                    page.PageCount = (int)(decimal.Ceiling((decimal)(page.RecordCount * 1.0 / page.PageSize)));

                    if (page.PageIndex <= 1) page.PageIndex = 1;
                    if (page.PageIndex >= page.PageCount) page.PageIndex = page.PageCount;

                    int startIndex = (page.PageIndex - 1) * page.PageSize;
                    int endIndex = startIndex + page.PageSize;

                    Command.CommandText = Command.CommandText.TrimEnd(';') + " Limit " + page.PageSize + " Offset  " + startIndex + "";

                }

                if (TransactionBegin)
                    dt = sqlserver.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
                else
                    dt = sqlserver.GetDataTable(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #endregion

    }
}
